package cn.thoughtworks.school.visualization;

import java.util.HashMap;
import java.util.Map;

public class Constant {
    private static Map<String, String> SQL = new HashMap<>();

    private static String FIND_PROGRAM_ASSIGNMENTS_STATUS_SQL = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select concat(detail.name,\"(@\",u.userName,\")\") name,task.title 任务卡,t.title 主题,(select group_concat(t.name,'|',t.color separator \",\") from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId\n" +
        "where uTag.programId=@programId and uTag.studentId=detail.userId group by uTag.studentId) as 'tag',a.title 作业,\n" +
        "case \n" +
        "     when reviewe.status = '已提交' then '已提交'\n" +
        "     when reviewe.status = '已完成' and reviewe.grade < 60 then '未达标'\n" +
        "     when reviewe.status = '已完成' and reviewe.grade >= 60 then '已完成'\n" +
        "     when reviewe.status = '优秀' then '已完成'\n" +
        "     else '未提交'\n" +
        "end\n" +
        "as 分数,IFNULL(uqz.assTag, 0) 已评阅,CONVERT_TZ (uad.startTime,  '+00:00','+08:00') startTime,CONVERT_TZ (uad.endTime,  '+00:00','+08:00') endTime from task \n" +
        "left join(select title,id,taskId,visible from assignment) a on task.id = a.taskId and a.visible=1 \n" +
        "right join(select id,title,orderNumber,visible from topic) t on t.id=task.topicId and t.visible=1 \n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "\n" +
        "left join(select userId,name from UserCenter.userDetail) detail on detail.userId = f.studentId\n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "left join(select * from userAssignmentDuration) uad on uad.userId=f.studentId and uad.taskId=task.id and uad.assignmentId=a.id\n" +
        "left join(select count(1) assTag,studentId,assignmentId from userAssignmentQuizTag group by studentId , assignmentId) uqz \n" +
        "    on uqz.studentId=detail.userId and uqz.assignmentId=a.id\n" +
        "left join(select status,assignmentId,studentId,grade from reviewQuiz) reviewe on reviewe.assignmentId = a.id and f.studentId=reviewe.studentId\n" +
        "where task.programId=@programId and task.visible=1 order by t.orderNumber ASC";
    public static String API_KEY = "RCyphNYDfFvTuwL8LrOvujFvcr6eVAKrmGbR0cnH";

    private static String FIND_ALL_ASSIGNMENTS_STUDENTS_STATUS = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "\n" +
        "select concat(detail.name,\"(@\",u.userName,\")\") 姓名,ass.id,t.title 任务卡,ass.title 作业,IFNULL(review.status,'未提交') 状态,review.grade 分数,IFNULL(uqz.assTag, 0) 已评阅,\n" +
        "(select group_concat(t.name,'|',t.color separator \",\") from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId\n" +
        "where uTag.programId=@programId and uTag.studentId=detail.userId group by uTag.studentId) as 'tag'\n" +
        "\n" +
        "from assignment ass \n" +
        "left join(select userId,name from UserCenter.userDetail) detail on detail.userId in (select studentId from follow where tutorId=@tutorId and programId=@programId)\n" +
        "left join (select id,userName from UserCenter.user) u on u.id=detail.userId\n" +
        "left join(select status,assignmentId,studentId,grade from reviewQuiz) review on review.studentId=detail.userId and review.assignmentId=ass.id\n" +
        "left join(select count(1) assTag,studentId,assignmentId from userAssignmentQuizTag group by studentId , assignmentId) uqz on uqz.studentId=detail.userId and uqz.assignmentId=ass.id\n" +
        "left join(select id,programId,orderNumber,visible from topic ) top on  top.programId = @programId and top.visible=1\n" +
        "left join(select id,topicId,title,visible,orderNumber from task ) t on  t.topicId = top.id and t.visible=1\n" +
        "WHERE ass.taskId =t.id and ass.visible=1 order by top.orderNumber,t.orderNumber,ass.orderNumber";
    private static String FIND_PROGRAM_ASSIGNMENTS_BASIC_QUIZ_STATUS = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select bq.assignmentId,b.description,b.type,bqc.choice,b.answer,f.studentId,bqs.isCorrect from assignmentQuiz bq\n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "left join(select assignmentId,quizId,userId,isCorrect from QuizCenter.basicQuizSubmission) bqs on bqs.assignmentId=bq.assignmentId and bqs.userId=f.studentId and \n" +
        "bqs.quizId=bq.quizId\n" +
        "left join(select id,description,answer,type from QuizCenter.basicQuiz) b on b.id = bq.quizId\n" +
        "left join(select basicQuizId,group_concat(choice separator '|') choice from QuizCenter.basicQuizChoices group by basicQuizId) bqc on bqc.basicQuizId=bq.quizId\n" +
        "where bq.assignmentId in (select id from assignment where type='BASIC_QUIZ' and  taskId in (select id from task where programId =@programId and visible=1) )\n";

    private static String FIND_PROGRAM_ASSIGNMENTS_BASIC_QUIZ_OVERVIEW = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select t.id taskId,ass.title assignmentTitle,ass.id assignmentId,f.studentId studentId,concat(detail.name,\"(@\",u.userName,\")\") userName,t.title taskTitle,\n" +
        "(select group_concat(t.name,'|',t.color separator ',') from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId \n" +
        "where uTag.programId=@programId and uTag.studentId=u.id group by uTag.studentId) as 'tag',\n" +
        "(select count(1) from QuizCenter.basicQuizSubmission where assignmentId=ass.id and userId=u.id and isCorrect=1 ) as 'correctCount',\n" +
        "(select count(1) from QuizCenter.basicQuizSubmission where assignmentId=ass.id and userId=u.id and isCorrect=0 ) as 'wrongCount',\n" +
        "(select count(1) from assignmentQuiz where assignmentId=ass.id) as 'total'\n" +
        "from assignment ass\n" +
        "\n" +
        "left join ( select * from follow) f on f.programId = @programId and f.tutorId=@tutorId \n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "left join(select name,userId from UserCenter.userDetail) detail on detail.userId =u.id\n" +
        "left join (select id,visible,programId,title from task) t on t.visible=1 and t.programId=@programId\n" +
        "where t.id = ass.taskId and ass.visible=1 and ass.type='BASIC_QUIZ'";
    private static String FIND_PROGRAM_ASSIGNMENT_BASIC_QUIZ_STATUS = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "set @assignmentId =ASSIGNMENT_ID;\n" +
        "select bq.assignmentId,b.description,b.type,bqc.choice,b.answer,f.studentId,bqs.isCorrect from assignmentQuiz bq\n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "left join(select assignmentId,quizId,userId,isCorrect from QuizCenter.basicQuizSubmission) bqs on bqs.assignmentId=bq.assignmentId and bqs.userId=f.studentId and \n" +
        "bqs.quizId=bq.quizId\n" +
        "left join(select id,description,answer,type from QuizCenter.basicQuiz) b on b.id = bq.quizId\n" +
        "left join(select basicQuizId,group_concat(choice separator '|') choice from QuizCenter.basicQuizChoices group by basicQuizId) bqc on bqc.basicQuizId=bq.quizId\n" +
        "where bq.assignmentId = @assignmentId \n";

    private static String FIND_PROGRAM_ASSIGNMENT_BASIC_QUIZ_STATUS_BY_TAG = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "set @assignmentId =ASSIGNMENT_ID;\n" +
        "set @tagId=TAG_ID;\n" +
        "select bq.assignmentId,b.description,b.type,bqc.choice,b.answer,f.studentId,bqs.isCorrect\n" +
        "\n" +
        "from assignmentQuiz bq\n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId and \n" +
        "f.studentId in (select studentId from userTag where tagId=@tagId)\n" +
        "left join(select assignmentId,quizId,userId,isCorrect from QuizCenter.basicQuizSubmission) bqs on bqs.assignmentId=bq.assignmentId and bqs.userId=f.studentId and \n" +
        "bqs.quizId=bq.quizId\n" +
        "left join(select id,description,answer,type from QuizCenter.basicQuiz) b on b.id = bq.quizId\n" +
        "left join(select basicQuizId,group_concat(choice separator '|') choice from QuizCenter.basicQuizChoices group by basicQuizId) bqc on bqc.basicQuizId=bq.quizId\n" +
        "where bq.assignmentId = @assignmentId \n";
    private static final String FIND_PROGRAM_ASSIGNMENTS_BASIC_QUIZ_OVERVIEW_BY_ASSIGNMENT = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "set @assignmentId=ASSIGNMENT_ID;\n" +
        "\n" +
        "select ass.title assignmentTitle,ass.id assignmentId,f.studentId studentId,u.userName,detail.name,\n" +
        "\n" +
        "(select group_concat(t.name,'|',t.color separator ',') from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId \n" +
        "where uTag.programId=@programId  and uTag.studentId=u.id group by uTag.studentId) as 'tag',\n" +
        "\n" +
        "(select count(1) from QuizCenter.basicQuizSubmission where assignmentId=ass.id and userId=u.id and isCorrect=1 ) as 'correctCount',\n" +
        "(select count(1) from QuizCenter.basicQuizSubmission where assignmentId=ass.id and userId=u.id and isCorrect=0 ) as 'wrongCount',\n" +
        "(select count(1) from assignmentQuiz where assignmentId=ass.id) as 'total'\n" +
        "from assignment ass\n" +
        "\n" +
        "left join ( select * from follow) f on f.programId = @programId and f.tutorId=@tutorId \n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "\nleft join (select userId,name from UserCenter.userDetail) detail on u.id=detail.userId\n" +
        "where ass.id = @assignmentId and ass.visible=1 and ass.type='BASIC_QUIZ'";


    private static final String FIND_PROGRAM_ASSIGNMENT_SURVEY_QUIZ_STATUS = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "\n" +
        "select t.id taskId,ass.title assignmentTitle,ass.id assignmentId,f.studentId studentId,concat(detail.name,\"(@\",u.userName,\")\") userName,t.title taskTitle,\n" +
        "(select group_concat(t.name,'|',t.color separator ',') from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId \n" +
        "where uTag.programId=@programId and uTag.studentId=u.id group by uTag.studentId) as 'tag',\n" +
        "sq.description,sqs.userAnswer,sqc.choice\n" +
        "\n" +
        "from assignment ass\n" +
        "\n" +
        "left join ( select * from follow) f on f.programId = @programId and f.tutorId=@tutorId \n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "left join(select id,description from QuizCenter.surveyQuiz) sq on sq.id in (select quizId from assignmentQuiz where assignmentId=ass.id)\n" +
        "left join(select assignmentId,quizId,userId,userAnswer from QuizCenter.surveyQuizSubmission) sqs on sqs.assignmentId=ass.id and sqs.userId=u.id and sqs.quizId=sq.id\n" +
        "left join(select name,userId from UserCenter.userDetail) detail on detail.userId =u.id\n" +
        "right join(select choice,surveyQuizId from QuizCenter.surveyQuizChoices)  sqc on sqc.surveyQuizId=sq.id\n" +
        "left join (select id,visible,programId,title from task) t on t.visible=1 and t.programId=@programId\n" +
        "where t.id = ass.taskId and ass.visible=1 and ass.type='SURVEY_QUIZ'";

    private static final String FIND_PROGRAM_ASSIGNMENT_TAG_STATUS = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select concat(detail.name,\"(@\",u.userName,\")\") userName, task.title taskTitle,concat(aqt.name,'|',aqt.color ) assTag,uqz.tagCount assTagCount,t.title topicTitle,(select group_concat(t.name,'|',t.color separator \",\") from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId\n" +
        "where uTag.programId=@programId and uTag.studentId=f.studentId group by uTag.studentId) as 'userTag',a.title assignmentTitle\n" +
        " from task \n" +
        "left join(select title,id,taskId,visible,orderNumber from assignment) a on task.id = a.taskId and a.visible=1 \n" +
        "right join(select id,title,orderNumber,visible from topic) t on t.id=task.topicId and t.visible=1 \n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "left join (select id,name,programId,color from assignmentQuizTag) aqt on aqt.programId=@programId\n" +
        "left join(select id,userName from UserCenter.user) u on u.id = f.studentId\n" +
        "left join(select userId,name from UserCenter.userDetail) detail on detail.userId = f.studentId\n" +
        "left join(select count(1) tagCount,assignmentId,studentId,assignmentQuizTagId from userAssignmentQuizTag group by assignmentQuizTagId,studentId,assignmentId) uqz \n" +
        "    on  uqz.assignmentId=a.id and uqz.studentId=f.studentId and aqt.id = uqz.assignmentQuizTagId\n" +
        "where task.programId=@programId and task.visible=1 order by t.orderNumber,task.orderNumber,a.orderNumber";

    private static final String FIND_PROGRAM_OVERVIEW_SQL = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select concat(detail.name,\"(@\",u.userName,\")\") name,t.title topicTitle,task.title taskTitle,\n" +
        "(select group_concat(t.name,'|',t.color separator \",\") from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId\n" +
        "where uTag.programId=@programId and uTag.studentId=detail.userId group by uTag.studentId) as 'tag',a.title assignmentTitle,\n" +
        "reviewe.grade as grade,aqt.tag assignmentTag from task \n" +
        "right join(select id,title,orderNumber,visible from topic) t on t.id=task.topicId and t.visible=1 \n" +
        "left join(select title,id,taskId,visible from assignment) a on task.id = a.taskId and a.visible=1 \n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "\n" +
        "left join(select userId,name from UserCenter.userDetail) detail on detail.userId = f.studentId\n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "\n" +
        "left join(select assignmentQuizTagId, studentId,assignmentId from userAssignmentQuizTag) uqz \n" +
        "    on uqz.studentId=detail.userId and uqz.assignmentId=a.id\n" +
        "left join(select group_concat(name,'|',color separator \",\") tag,id  from assignmentQuizTag group by id) aqt \n" +
        "    on aqt.id = uqz.assignmentQuizTagId\n" +
        "    \n" +
        "left join(select status,assignmentId,studentId,grade from reviewQuiz) reviewe on reviewe.assignmentId = a.id and f.studentId=reviewe.studentId\n" +
        "where task.programId=@programId and task.visible=1 order by t.orderNumber ASC, task.orderNumber ASC,task.orderNumber ASC";

    private static final String FIND_PROGRAM_ASSIGNMENT_FINISH_SPEED_OVERVIEW = "set @programId=PROGRAM_ID;\n" +
        "set @tutorId=TUTOR_ID;\n" +
        "select concat(detail.name,\"(@\",u.userName,\")\") name,task.id taskId,task.title 任务卡,t.id topicId,t.title 主题,(select group_concat(t.name,'|',t.color separator \",\") from userTag uTag\n" +
        "left join (select name,id,color from tag) t on t.id = uTag.tagId\n" +
        "where uTag.programId=@programId and uTag.studentId=detail.userId group by uTag.studentId) as 'tag',a.title 作业,a.id assignmentId,\n" +
        "UNIX_TIMESTAMP (uad.startTime) startTimestamp,UNIX_TIMESTAMP (uad.endTime) endTimestamp," +
        "CONVERT_TZ (uad.startTime,  '+00:00','+08:00') startTime,CONVERT_TZ (uad.endTime,  '+00:00','+08:00') endTime from task \n" +
        "left join(select title,id,taskId,visible from assignment) a on task.id = a.taskId and a.visible=1 \n" +
        "right join(select id,title,orderNumber,visible from topic) t on t.id=task.topicId and t.visible=1 \n" +
        "left join(select tutorId,programId,studentId from follow) f on f.tutorId = @tutorId and f.programId=@programId\n" +
        "\n" +
        "left join(select userId,name from UserCenter.userDetail) detail on detail.userId = f.studentId\n" +
        "left join (select id,userName from UserCenter.user) u on u.id=f.studentId\n" +
        "left join(select * from userAssignmentDuration) uad on uad.userId=f.studentId and uad.taskId=task.id and uad.assignmentId=a.id\n" +
        "where task.programId=@programId and task.visible=1 order by t.orderNumber ASC";

    static {
        SQL.put("训练营总览", FIND_PROGRAM_OVERVIEW_SQL);
        SQL.put("训练营作业情况", FIND_PROGRAM_ASSIGNMENTS_STATUS_SQL);
        SQL.put("学员作业完成率概览", FIND_ALL_ASSIGNMENTS_STUDENTS_STATUS);
        SQL.put("学员客观题作业完成情况", FIND_PROGRAM_ASSIGNMENT_BASIC_QUIZ_STATUS);
        SQL.put("学员客观题总览", FIND_PROGRAM_ASSIGNMENTS_BASIC_QUIZ_OVERVIEW);
        SQL.put("学员客观题总览-byAssignment", FIND_PROGRAM_ASSIGNMENTS_BASIC_QUIZ_OVERVIEW_BY_ASSIGNMENT);
        SQL.put("学员客观题作业完成情况-byTag", FIND_PROGRAM_ASSIGNMENT_BASIC_QUIZ_STATUS_BY_TAG);
        SQL.put("学员调研题总览", FIND_PROGRAM_ASSIGNMENT_SURVEY_QUIZ_STATUS);
        SQL.put("作业Tag总览", FIND_PROGRAM_ASSIGNMENT_TAG_STATUS);
        SQL.put("作业完成速度总览", FIND_PROGRAM_ASSIGNMENT_FINISH_SPEED_OVERVIEW);
    }

    public static String getSQL(String type) {
        return SQL.get(type);
    }


}
